/* 
 *   Classname:  datapower.ExportToCSV
 * 
 *   Date       Developer   Description
 *   Nov 28, 2017    周成功              新建
 *
 */
package demo.database.csv;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.titan.core.utils.data.FormatUtils;
import com.titan.core.utils.data.StringUtils;
import com.titan.core.utils.db.DBUtils;
import com.titan.core.utils.db.DataSourceUtils;
import com.titan.core.utils.db.JdbcUtils;
import com.titan.core.utils.dbinfo.dialect.DBDialect;
import com.titan.core.utils.dbinfo.dialect.IDBDialect;
import com.titan.core.utils.file.CSVUtils;
import com.titan.core.utils.file.FileUtils;
import com.titan.core.utils.io.ConsoleUtils;
import com.titan.core.utils.object.TableColumnDescriptor;
import com.titan.core.utils.object.TableDescriptor;

/**
 *
 *
 * @author 周成功
 *
 */
public class MysqlToCSV {
    private static String rootPath  = null;
    private static String ip  = null;
    private static String dbname  = null;
    
    private static void localEnv() {
        ip  = "127.0.0.1";
        dbname = "s1415";
        rootPath = "E:\\ZNGJK\\temp\\";
    }

    public static void main(String[] args) {
        Connection conn = null;
        try {
            localEnv();
          
            String username = "root";
            String password = "123456";
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://" + ip + ":3306/" + dbname;
            DataSource datasource = DataSourceUtils.registerDataSource(null, driver, url, username, password, 5, 5);
            JdbcUtils.setDataSource(datasource);
            conn = datasource.getConnection();

            IDBDialect db = DBDialect.MYSQL.get();
            List<String> tables = db.getTables(conn, dbname);

            String batchno = FormatUtils.getSysdate("yyyy-MM-dd HH.mm.ss");
            for(int i=0; i<tables.size(); i++) {
                ConsoleUtils.log("tablename", tables.get(i));
                run(db, conn, tables.get(i), batchno);
            }
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            DBUtils.close(conn);
        }
    }
    private static void run(IDBDialect db, Connection conn, String tablename, String batchno) throws Exception {
        List<String[]> lines = new ArrayList<String[]>();
        
        TableDescriptor table = db.getTable(conn, dbname, tablename);
        List<TableColumnDescriptor> columns = table.getColumns().valueList();
        int columnCount = columns.size();
        String[] hearder = new String[columnCount];
        for (int i = 0; i < columnCount; i++) {
            TableColumnDescriptor column = columns.get(i);
//            ConsoleUtils.log(tablename, "JavaType", column.getJavaType().getName(), "comment", column.getComments());
            hearder[i] = column.getNameEnglish();
        }
        lines.add(hearder);
        
        String sql = "select * from " + tablename;
        List<Map<String, Object>> rows = JdbcUtils.queryToList(sql);
        try {
            for (int i = 0; i < rows.size(); i++) {
                Map<String,Object> row = rows.get(i);
                String[] data = new String[columnCount];
                for (int j = 0; j < columnCount; j++) {
                    TableColumnDescriptor column = columns.get(j);
                    String field = column.getNameEnglish();
                    Object object = row.get(field);
                    if(StringUtils.isNullOrEmpty(object)) {
                        data[j] = "";
                    } else {
                        String clazz = object.getClass().getName();
                        if("java.sql.Timestamp".equals(clazz)) {
                            java.sql.Timestamp value = (java.sql.Timestamp)object;
                            data[j] = FormatUtils.dateToString(value, "yyyy-MM-dd HH:mm:ss");
                        } else if("java.lang.String".equals(clazz)) {
                            data[j] = FormatUtils.toString(object);
                        } else if("java.math.BigDecimal".equals(clazz)) {
                            data[j] = FormatUtils.toString(object);
                        } else {
                            data[j] = FormatUtils.toString(object);
                        }
                    }
                }
                lines.add(data);
            }
        } finally {
            rows.clear();
            rows = null;
        }
        String file = rootPath + batchno + "\\" + tablename + ".csv";
        FileUtils.create(file);
        CSVUtils.write(file, lines);
    }
}
